/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.business.repository;

import me.zhengjie.business.domain.Hljg;
import me.zhengjie.business.domain.Sbgs;
import me.zhengjie.business.domain.vo.HljgDspVo;
import me.zhengjie.business.domain.vo.KB03;
import me.zhengjie.business.domain.vo.KB22;
import me.zhengjie.business.domain.vo.Kc21Vo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
* @website https://el-admin.vip
* @author xiahongbo
* @date 2022-04-13
**/
public interface SbgsRepository extends JpaRepository<Sbgs, String>, JpaSpecificationExecutor<Sbgs> {

    /**
     * 根据用户ID查询
     * @param bisGsglUserId 用户ID
     * @return /
     */
    Sbgs findByBisGsglUserId(Long bisGsglUserId);



    @Query(value = "select new me.zhengjie.business.domain.vo.KB03( " +
            "       t.aab034," +
            "       t.akb020," +
            "       t.akb021," +
            "       t.aae002," +
            "       t.aae015," +
            "       t.cjt100," +
            "       t.akb062," +
            "       t.ckb152," +
            "       t.ckb151," +
            "       t.ckb153," +
            "       t.akb068," +
            "       t.zkb060," +
            "       t.tsryzf," +
            "       t.ckb156," +
            "       t.ckb208," +
            "       t.yjbjdx," +
            "       t.akb064," +
            "       t.ckb161," +
            "       t.ckb169," +
            "       t.insuNo," +
            "       t.insuName," +
            "       t.id," +
            "       t.aae016) " +
            "       from Bfd t " +
            "       where 1=1 " +
            "       and (?1 IS NULL OR ?1 = '' OR t.akb020 = ?1 ) " +
            "       and (?2 IS NULL OR ?2 = '' OR t.aae002 = ?2 ) " +
            "       and (?3 IS NULL OR ?3 = '' OR t.insuNo = ?3 ) ")
    List<KB03> queryKb03(String akb020, String aae002, String insuNo);



    @Query(value = "select new me.zhengjie.business.domain.vo.KB22( " +
            "       t.id," +
            "       t.akb020," +
            "       t.aae002," +
            "       t.aae015," +
            "       t.cjt100," +
            "       t.aab034," +
            "       t.aac001," +
            "       t.aac003," +
            "       t.aac002," +
            "       t.akc192," +
            "       t.akc194," +
            "       t.akc264," +
            "       t.akc260," +
            "       t.akc255," +
            "       t.akc261," +
            "       t.aaa005," +
            "       t.yl," +
            "       t.dqbo," +
            "       t.zkc024," +
            "       t.akc268," +
            "       t.kjsl," +
            "       t.hlkjje," +
            "       t.hckjje," +
            "       t.akc265," +
            "       t.ckc283," +
            "       t.adlTotal," +
            "       t.aae013," +
            "       t.aaa022) " +
            "       from Bfdmx t " +
            "       where 1=1 " +
            "       and (?1 IS NULL OR ?1 = '' OR t.akb020 = ?1 ) " +
            "       and (?2 IS NULL OR ?2 = '' OR t.aae002 like ?2||'%' ) " +
            "       and (?3 IS NULL OR ?3 = '' OR t.cjt100 = ?3 ) " +
            "       and (?4 IS NULL OR ?4 = '' OR t.aaa022 = ?4 ) " +
            "       and (?5 IS NULL OR ?5 = '' OR t.akb020 in (select distinct r.akb020 from Bfd r where r.insuNo = ?5) ) " +
            "       and (?6 IS NULL OR ?6 = '' OR t.aab034 = ?6 ) ")
    List<KB22> queryKb22(String akb020, String aae002, String cjt100, String aaa022, String insuNo, String aab034);

    /**
     * 查询商保公司下属机构
     * @param sbId
     * @param jgxx
     * @param pageable
     * @return
     */
    @Query(value = "select new me.zhengjie.business.domain.vo.HljgDspVo( " +
            "r2.bisGsglId, " +
            "r2.bisGsglQymc, " +
            "r2.bisGsglYljgdm, " +
            "count(1), " +
            "(case when r2.sbgs.bisGsglId=r1.bisSbgsId then '1' else '0' end) as flag) " +
            "from Zhsq r1 " +
            " inner join Hljg r2 on r1.hljg.bisGsglId = r2.bisGsglId " +
            " where  1=1 " +
            "   and r1.bisZhsqZt in (?4) " +
            "   and r1.bisZhsqZhlb = ?1 " +
            "   and r1.bisSbgsId = ?2 " +
            "   and (?3 IS NULL OR ?3 = '' OR r2.bisGsglYljgdm = ?3 OR r2.bisGsglQymc like '%'||?3||'%' ) " +
            " group by r2.bisGsglId, r2.bisGsglQymc, r2.bisGsglYljgdm, r2.sbgs.bisGsglId, r1.bisSbgsId " +
            " order by flag desc, r2.bisGsglYljgdm asc ",
            countQuery = "select count(*) from Zhsq r1 " +
                    " inner join Gsgl r2 on r1.hljg.bisGsglId = r2.bisGsglId " +
                    " where 1=1 " +
                    "   and r1.bisZhsqZt in (?4) " +
                    "   and r1.bisZhsqZhlb = ?1 " +
                    "   and r1.bisSbgsId = ?2 " +
                    "   and (?3 IS NULL OR ?3 = '' OR r2.bisGsglYljgdm = ?3 OR r2.bisGsglQymc like '%'||?3||'%' ) " +
                    " group by r2.bisGsglId, r2.bisGsglQymc, r2.bisGsglYljgdm, r2.sbgs.bisGsglId, r1.bisSbgsId ")
    Page<HljgDspVo> queryHljgCntVoPageList(String zhlb, String sbId, String jgxx, List<String> zt, Pageable pageable);
}